<?php

namespace app\service;

use app\exception\HttpResponseException;
use app\model\TaskModel;
use app\tool\SingletonTrait;
use support\Db;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;

class WorkbenchHistoryService
{
    use SingletonTrait;

    public function __construct()
    {

    }

    /**
     * 历史部门交付情况
     * @param int $projectId
     * @param $userId
     * @param array $todoStepCategoryIdList
     * @param int $startTime
     * @param int $endTime
     * @return array
     */
    public function groupDeliveryHistoryCount(int $projectId, $userId, array $todoStepCategoryIdList, int $startTime, int $endTime): array
    {
        // 查询交付
        $taskSql = $this->getTaskDeliveryHistorySql($todoStepCategoryIdList, $projectId, $userId, $startTime, $endTime);
        // 发布
        $delayOrderTaskSql = $this->getPublishedTaskSql($todoStepCategoryIdList, $projectId, $userId, $startTime, $endTime);
        $data = Db::query("select sum(delivery_done) as delivery_done,sum(delivery_total) as delivery_total,end_time from ($taskSql union $delayOrderTaskSql) as tmp group by end_time");
        if (empty($data)) {
            return [];
        }
        return array_map(function ($tmp) {
            $tmp['delivery_done'] = intval($tmp['delivery_done']);
            $tmp['delivery_total'] = intval($tmp['delivery_total']);
            return $tmp;
        }, $data);
    }

    /**
     * 交付sql
     * @param $todoStepCategoryIdList
     * @param $projectId
     * @param $userId
     * @param $startTime
     * @param $endTime
     * @return mixed
     */
    public function getTaskDeliveryHistorySql($todoStepCategoryIdList, $projectId, $userId, $startTime, $endTime)
    {
        $finalStatusId = StatusService::getInstance()->byCode('final')['id'];
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $taskQuery = (new \app\model\TaskModel)->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', $closedStatusId);
        if (!empty($todoStepCategoryIdList)) {
            $taskQuery->whereIn('r_step_category.link_id', $todoStepCategoryIdList)
                ->join(['task_relation_tag' => 'r_step_category'], "r_step_category.link_module_code in ('step_category', 'show_step_category') and r_step_category.task_id = task.id");
        }
        if (!empty($userId)) {
            $taskQuery->whereIn('task_member.partner', $userId)
                ->join('task_member', 'task_member.task_id = task.id');
        }
        if (!empty($projectId)) {
            $taskQuery->where('task.project_id', $projectId);
        }
        if (!empty($startTime) && !empty($endTime)) {
            $taskQuery->whereRaw(sprintf("task.plan_delivery_time between %d and %d", $startTime, $endTime));
        }
        $taskField = [
            'count(`task`.`id`) as delivery_total',
            "from_unixtime(task.plan_delivery_time, '%Y-%m-%d') end_time",
            "count(get_json_string(`task`.`json`, '$.task_status') = $finalStatusId or null) as delivery_done"
        ];
        return $taskQuery->field($taskField)->group("from_unixtime(task.plan_delivery_time, '%Y-%m-%d')")->buildSql();
    }

    /**
     * 延期单交付sql
     * @param $todoStepCategoryIdList
     * @param $projectId
     * @param $userId
     * @param $startTime
     * @param $endTime
     * @return mixed
     */
    public function getDelayOrderTaskSql($todoStepCategoryIdList, $projectId, $userId, $startTime, $endTime)
    {
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $delayOrderTaskQuery = (new \app\model\TaskModel)->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', $closedStatusId);
        $delayOrderTaskQuery->join('delay_order_task dot', ' task.id = dot.task_id');
        if (!empty($todoStepCategoryIdList)) {
            $delayOrderTaskQuery->whereIn('r_step_category.link_id', $todoStepCategoryIdList)
                ->join(['task_relation_tag' => 'r_step_category'], "r_step_category.link_module_code in ('step_category', 'show_step_category') and r_step_category.task_id = task.id");
        }
        if (!empty($startTime) && !empty($endTime)) {
            $delayOrderTaskQuery->whereRaw(sprintf('dot.original_plan_time between %d and %d', $startTime, $endTime));
        }
        if (!empty($projectId)) {
            $delayOrderTaskQuery->where('task.project_id', $projectId);
        }
        if (!empty($userId)) {
            $delayOrderTaskQuery->whereIn('task_member.partner', $userId)
                ->join('task_member', 'task_member.task_id = task.id');
        }
        $delayOrderTaskField = [
            'task.id task_id',
            "from_unixtime(dot.original_plan_time, '%Y-%m-%d') end_time",
            "get_json_string(`task`.`json`, '$.task_status') task_status"
        ];
        return $delayOrderTaskQuery->field($delayOrderTaskField)->buildSql();
    }

    /**
     * 发布任务sql
     * @param $todoStepCategoryIdList
     * @param $projectId
     * @param $userId
     * @param $startTime
     * @param $endTime
     * @return mixed
     */
    public function getPublishedTaskSql($todoStepCategoryIdList, $projectId, $userId, $startTime, $endTime)
    {
        $finalStatusId = StatusService::getInstance()->byCode('final')['id'];
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $versionModuleId = ModuleService::getInstance()->byCode('version')['id'];
        $delayOrderTaskQuery = (new \app\model\TaskModel)->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', $closedStatusId);
        $delayOrderTaskQuery->join('version v', 'task.id = v.link_id and v.module_id = ' . $versionModuleId);
        if (!empty($todoStepCategoryIdList)) {
            $delayOrderTaskQuery->whereIn('r_step_category.link_id', $todoStepCategoryIdList)
                ->join(['task_relation_tag' => 'r_step_category'], "r_step_category.link_module_code in ('step_category', 'show_step_category') and r_step_category.task_id = task.id");
        }
        if (!empty($startTime) && !empty($endTime)) {
            $delayOrderTaskQuery->whereRaw(sprintf('v.published between %d and %d', $startTime, $endTime));
        }
        if (!empty($projectId)) {
            $delayOrderTaskQuery->where('task.project_id', $projectId);
        }
        if (!empty($userId)) {
            $delayOrderTaskQuery->whereIn('task_member.partner', $userId)
                ->join('task_member', 'task_member.task_id = task.id');
        }
        $delayOrderTaskField = [
            'count(`task`.`id`) as delivery_total',
            "from_unixtime(v.published, '%Y-%m-%d') end_time",
            "count(get_json_string(`task`.`json`, '$.task_status') = $finalStatusId or null) as delivery_done"
        ];
        return $delayOrderTaskQuery->field($delayOrderTaskField)->group("from_unixtime(v.published, '%Y-%m-%d')")->buildSql();
    }

    /**
     * 历史完成情况
     * @param int $projectId
     * @param $userId
     * @param array $todoStepCategoryIdList
     * @param int $startTime
     * @param int $endTime
     * @return array
     */
    public function groupGroupPlanDoneCountHistoryCount(int $projectId, $userId, array $todoStepCategoryIdList, int $startTime, int $endTime)
    {
        // 查询完成
        $taskPlanSql = $this->getTaskPlanHistorySql($todoStepCategoryIdList, $projectId, $userId, $startTime, $endTime);
        // 发布
        $publishedTaskSql = $this->getDelayOrderTaskSql($todoStepCategoryIdList, $projectId, $userId, $startTime, $endTime);

        $finalStatusId = StatusService::getInstance()->byCode('final')['id'];
        $data = Db::query("select count(task_id)  task_plan_total,count(task_status = {$finalStatusId} or null)  task_plan_done,end_time from ($taskPlanSql union $publishedTaskSql) as tmp group by end_time");
        if (empty($data)) {
            return [];
        }
        return array_map(function ($tmp) {
            $tmp['task_plan_total'] = intval($tmp['task_plan_total']);
            $tmp['task_plan_done'] = intval($tmp['task_plan_done']);
            return $tmp;
        }, $data);
    }

    /**
     * 完成情况
     * @param $todoStepCategoryIdList
     * @param $projectId
     * @param $userId
     * @param $startTime
     * @param $endTime
     * @return mixed
     */
    public function getTaskPlanHistorySql($todoStepCategoryIdList, $projectId, $userId, $startTime, $endTime)
    {
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $taskQuery = (new \app\model\TaskModel)->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', $closedStatusId);
        if (!empty($todoStepCategoryIdList)) {
            $taskQuery->whereIn('r_step_category.link_id', $todoStepCategoryIdList)
                ->join(['task_relation_tag' => 'r_step_category'], "r_step_category.link_module_code in ('step_category', 'show_step_category') and r_step_category.task_id = task.id");
        }
        if (!empty($userId)) {
            $taskQuery->whereIn('task_member.partner', $userId)
                ->join('task_member', 'task_member.task_id = task.id');
        }
        if (!empty($projectId)) {
            $taskQuery->where('task.project_id', $projectId);
        }
        if (!empty($startTime) && !empty($endTime)) {
            $taskQuery->whereRaw(sprintf("task.plan_end_time between %d and %d", $startTime, $endTime));
        }
        $taskField = [
            'task.id task_id',
            "from_unixtime(task.plan_end_time, '%Y-%m-%d') end_time",
            "get_json_string(`task`.`json`, '$.task_status') task_status"
        ];
        return $taskQuery->field($taskField)->buildSql();
    }

    /**
     * 待处理工单
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]|null
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     * @throws HttpResponseException
     */
    public function getPendingTaskWorkOrderHistoryCount($param, $saasUserId, $tenantId): ?array
    {
        $db = (new \app\model\TaskWorkOrder)->field("count(distinct task_work_order.id) as number," . "from_unixtime(task_work_order.created,'%Y-%m-%d') as date")
            ->join('task', 'task.id = task_work_order.task_id')
            ->join('task_work_order_user', 'task_work_order_user.work_order_id = task_work_order.id');

        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        if (!empty($closedId)) {
            $db->whereFieldRaw("get_json_string(task.`json`, 'task_status')", '<>', $closedId);
        }
        if (!empty($param['project_id'])) {
            $db->where('task.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return null;
            }
            $db->whereIn('task.project_id', $imUserProjectIds);
        }

        if (!empty($param['step_category_id'])) {
            $db->whereIn('task_work_order.step_category_id', $param['step_category_id']);
        }
        // 我负责的传部门id
        $distinctUserId = [];
        if (!empty($param['department_id'])) {
            $departmentUserData = UserService::getInstance()->getDepartmentUserMultiDepartmentUser([$param['department_id']], $tenantId);
            $distinctUserId = $departmentUserData['distinct_user_ids'];
        }
        if (empty($param['step_category_id']) || !empty($param['department_id'])) {
            if (!empty($distinctUserId)) {
                $saasUserId = $distinctUserId;
            }
            if (!empty($saasUserId)) {
                if (!is_array($saasUserId)) {
                    $saasUserId = [$saasUserId];
                }
                $db->whereIn('task_work_order_user.user_id', $saasUserId);
            }
        }

//        // 计划完成时间筛选
//        if (!empty($param['start_time'])) {
//            $db->whereFieldRaw('task.plan_end_time', '>=', $param['start_time']);
//        }
//        if (!empty($param['end_time'])) {
//            $db->whereFieldRaw('task.plan_end_time', '<=', $param['end_time']);
//        }
        // 计划完成时间筛选
        if (!empty($param['start_time'])) {
            $db->whereFieldRaw('task_work_order.created', '>=', $param['start_time']);
        }
        if (!empty($param['end_time'])) {
            $db->whereFieldRaw('task_work_order.created', '<=', $param['end_time']);
        }
        // 工单状态范围限制
        $untreatedString = OptionsService::getInstance()->getOptionsConfigItemData('task_work_order_status_config', 'untreated');
        if (empty($untreatedString)) {
            return null;
        }
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $untreatedStatus = explode(',', $untreatedString);
        $taskWorkOrderData = $db
            ->where('task_work_order.tenant_id', $tenantId)
            ->whereIn('task_work_order.status', $untreatedStatus)
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
            ->whereFieldRaw("get_json_string(task.`json`,'task_status')", '<>', $closedStatusId)
//            ->where('task_work_order_user.user_id', $saasUserId)
            ->group("date")
            ->select();

        if (empty($taskWorkOrderData->toArray())) {
            return null;
        }
        $returnData = [];
        foreach ($taskWorkOrderData->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($param['project_id'] ?? 0),
                'date' => $item['date'],
            ];
        }
        return $returnData;
    }
}
